use admin
/*
drop TABLE dbo.showcontig
if object_id('showcontig','U') is null
	CREATE TABLE dbo.showcontig (DatabaseName varchar(30),
				TableName varchar(40), 
				PagesScanned int, 
				ExtentsScanned int, 
				ExtentSwitches int,
				AvgPagesPerExtent numeric(5,2),
				ScanDensity numeric(5,2),
				BestCount int,
				ActualCount int,
				ExtentScanFragmentation numeric(5,2),
				AvgBytesFreePerPage numeric(5,1),
				AvgPageDensity numeric(5,2),
				RecCreatedDt datetime)

*/
go 
if object_id('dbo.showcontig_to_table','P') is not null
	drop procedure dbo.showcontig_to_table
go
create procedure dbo.showcontig_to_table
as
declare @dbName varchar(30),
	@sqlstr nvarchar(4000)

set nocount on

declare dbcur cursor for
select name 
from master.dbo.sysdatabases
where name not in ('master','model','tempdb','msdb','pubs','Northwind','admin','distribution') 

open dbcur
fetch next from dbcur into @dbName
while @@fetch_status = 0
	begin
		set @sqlstr = 'set nocount on
declare @id int, @tableName varchar(40), @cmd varchar(1000)
declare tabcur cursor for
select table_name 
from ' + @dbName + '.INFORMATION_SCHEMA.Tables 
where table_type = ''base table''
and table_name not like ''dt_%''
and table_name not in (''MSpub_identity_range'',
			''sysarticles'',
			''sysarticleupdates'',
			''syspublications'',
			''sysschemaarticles'',
			''syssubscriptions'',
			''systranschemas'',
			''showcontig'')

open tabcur
fetch next from tabcur into @tableName

while @@fetch_status = 0
	begin
		if object_id(''tempdb.dbo.#showcontig'',''U'') is not null
			drop table #showcontig
		create table #showcontig (result varchar(80))
		set @cmd = ''osql -S'' + @@servername + '' -E -d' + @dbname + ' -Q'' + ''"'' + ''declare @id int select @id = object_id('''''' + @tablename + '''''') dbcc showcontig (@id)''
		insert #showcontig exec master.dbo.xp_cmdshell @cmd

		insert admin.dbo.showcontig 	(DatabaseName,
						TableName, 
						PagesScanned, 
						ExtentsScanned, 
						ExtentSwitches,
						AvgPagesPerExtent, 
						ScanDensity, 
						BestCount,
						ActualCount,
						ExtentScanFragmentation, 
						AvgBytesFreePerPage, 
						AvgPageDensity,
						RecCreatedDt)
		select DatabaseName = ''' + @dbName + ''', 
		TableName = substring(result, charindex(''Table:'',result) + 8, charindex('' ('',result) - 10), 
		PagesScanned = (select cast(substring(result,charindex(''.:'', result) + 3,datalength(result)) as int) 
		from #showcontig where result like ''- Pages Scanned%''),
		ExtentsScanned = (select cast(substring(result,charindex(''.:'', result) + 3,datalength(result)) as int) 
		from #showcontig where result like ''- Extents Scanned%''),
		ExtentSwitches = (select cast(substring(result,charindex(''.:'', result) + 3,datalength(result)) as int)
		from #showcontig where result like ''- Extent Switches%''),
		AvgPagesPerExtent =(select cast(substring(result,charindex(''.:'', result) + 3,datalength(result)) as numeric(5,2))
		from #showcontig where result like ''- Avg. Pages per Extent%''),
		ScanDensity = (select replace(substring(result,charindex(''.:'', result) + 3,charindex(''%'',result) - charindex(''.:'', result) - 3),''%'','''')
		from #showcontig where result like ''- Scan Density%''),
		BestCount = (select cast(reverse(substring(reverse(result),
				charindex('':'', reverse(result)) + 1,
				charindex(''['',reverse(result)) -1 - charindex('':'', reverse(result)))) as int)
		from #showcontig where result like ''- Scan Density%''),
		ActualCount = (select cast(reverse(substring(reverse(result),2,charindex('':'', reverse(result))-2)) as int)
		from #showcontig where result like ''- Scan Density%''),
		ExtentScanFragmentation = (select cast(replace(substring(result,charindex(''.:'', result) + 3,datalength(result)),''%'','''') as numeric(5,2))
		from #showcontig where result like ''- Extent Scan Fragmentation%''),
		AvgBytesFreePerPage = (select cast(substring(result,charindex(''.:'', result) + 3,datalength(result)) as numeric(5,1))
		from #showcontig where result like ''- Avg. Bytes Free per Page%''),
		AvgPageDensity = (select cast(replace(substring(result,charindex(''.:'', result) + 3,datalength(result)),''%'','''') as numeric(5,2))
		from #showcontig where result like ''- Avg. Page Density (full)%''),
		RecCreatedDt = getdate()
		from #showcontig where result like ''Table:%''
		drop table #showcontig
		fetch next from tabcur into @tableName
	end
deallocate tabcur
'
		exec sp_executesql @sqlstr
 
		fetch next from dbcur into @dbName
end
deallocate dbcur

--select * from dbo.showcontig
--select * from dbo.showcontig where scandensity < 80.0 and pagesscanned > 100
--select * from dbo.showcontig where scandensity < 80.0 and pagesscanned < 100